import requests
import json
import os
import schedule
import time
from datetime import datetime
from app.utils.db_utils import MySQLHelper

import logging

# 接口地址
API_URL = "https://v2.xxapi.cn/api/exchange"

logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')
# 货币-国家/地区映射字典
CURRENCY_COUNTRY_MAP = {
    # 主要货币
    "CNY": "中国", "USD": "美国", "EUR": "欧元区（德国、法国等）", "GBP": "英国",
    "JPY": "日本", "AUD": "澳大利亚", "CAD": "加拿大", "CHF": "瑞士",
    "HKD": "中国香港", "SGD": "新加坡",
    # 亚洲货币
    "KRW": "韩国", "INR": "印度", "IDR": "印度尼西亚", "THB": "泰国", "MYR": "马来西亚",
    "PHP": "菲律宾", "VND": "越南", "TWD": "中国台湾", "PKR": "巴基斯坦", "BDT": "孟加拉国",
    "LKR": "斯里兰卡", "MMK": "缅甸", "NPR": "尼泊尔", "KHR": "柬埔寨", "LAK": "老挝",
    "MNT": "蒙古", "SYP": "叙利亚", "IRR": "伊朗", "IQD": "伊拉克", "BHD": "巴林",
    "KWD": "科威特", "OMR": "阿曼", "QAR": "卡塔尔", "SAR": "沙特阿拉伯", "AED": "阿联酋",
    "TRY": "土耳其", "ILS": "以色列", "JOD": "约旦", "LBP": "黎巴嫩", "CYN": "无效货币",
    # 欧洲货币
    "RUB": "俄罗斯", "SEK": "瑞典", "NOK": "挪威", "DKK": "丹麦", "PLN": "波兰",
    "CZK": "捷克", "HUF": "匈牙利", "RON": "罗马尼亚", "BGN": "保加利亚", "HRK": "克罗地亚",
    "ISK": "冰岛", "MKD": "北马其顿", "ALL": "阿尔巴尼亚", "AMD": "亚美尼亚", "AZN": "阿塞拜疆",
    "BAM": "波黑", "BYN": "白俄罗斯", "DZD": "阿尔及利亚", "EEK": "爱沙尼亚（旧货币）",
    "GEL": "格鲁吉亚", "KGS": "吉尔吉斯斯坦", "KZT": "哈萨克斯坦", "MDL": "摩尔多瓦",
    "RSD": "塞尔维亚", "TJS": "塔吉克斯坦", "TMT": "土库曼斯坦", "UZS": "乌兹别克斯坦", "YER": "也门",
    # 美洲货币
    "ARS": "阿根廷", "BRL": "巴西", "CLP": "智利", "COP": "哥伦比亚", "CRC": "哥斯达黎加",
    "CUP": "古巴", "DOP": "多米尼加", "GTQ": "危地马拉", "HNL": "洪都拉斯", "JMD": "牙买加",
    "MXN": "墨西哥", "NIO": "尼加拉瓜", "PAB": "巴拿马", "PEN": "秘鲁", "PYG": "巴拉圭",
    "SVC": "萨尔瓦多", "UYU": "乌拉圭", "VES": "委内瑞拉",
    # 非洲货币
    "ZAR": "南非", "EGP": "埃及", "NGN": "尼日利亚", "KES": "肯尼亚", "GHS": "加纳",
    "UGX": "乌干达", "TZS": "坦桑尼亚", "MWK": "马拉维", "MZN": "莫桑比克", "ZMW": "赞比亚",
    "RWF": "卢旺达", "BWP": "博茨瓦纳", "XOF": "西非法郎区", "XAF": "中非法郎区", "XCD": "东加勒比",
    "XDR": "IMF特别提款权", "XAU": "黄金", "XAG": "白银", "XPT": "铂金", "XPD": "钯金",
    "AOA": "安哥拉", "BIF": "布隆迪", "CVE": "佛得角", "DJF": "吉布提", "ERN": "厄立特里亚",
    "ETB": "埃塞俄比亚", "GMD": "冈比亚", "GNF": "几内亚", "LYD": "利比亚", "MAD": "摩洛哥",
    "MGA": "马达加斯加", "MRO": "毛里塔尼亚（旧货币）", "MUR": "毛里求斯", "NAD": "纳米比亚",
    "SCR": "塞舌尔", "SDG": "苏丹", "SLL": "塞拉利昂", "SOS": "索马里", "STD": "圣多美（旧货币）",
    "TND": "突尼斯", "UAH": "乌克兰", "XPF": "法属波利尼西亚",
    # 大洋洲货币
    "NZD": "新西兰", "FJD": "斐济", "PGK": "巴布亚新几内亚", "WST": "萨摩亚"
}

# 去重后的货币列表
COMMON_CURRENCIES = [
    # 主要货币
    "CNY", "USD", "EUR", "GBP", "JPY", "AUD", "CAD", "CHF", "HKD", "SGD",
    # 亚洲货币
    "KRW", "INR", "IDR", "THB", "MYR", "PHP", "VND", "TWD", "PKR", "BDT",
    "LKR", "MMK", "NPR", "KHR", "LAK", "MNT", "SYP", "IRR", "IQD", "BHD",
    "KWD", "OMR", "QAR", "SAR", "AED", "TRY", "ILS", "JOD", "LBP", "CYN",
    # 欧洲货币
    "RUB", "SEK", "NOK", "DKK", "PLN", "CZK", "HUF", "RON", "BGN", "HRK",
    "ISK", "MKD", "ALL", "AMD", "AZN", "BAM", "BYN", "DZD", "EEK", "GEL",
    "KGS", "KZT", "MDL", "RSD", "TJS", "TMT", "UZS", "YER",
    # 美洲货币
    "ARS", "BRL", "CLP", "COP", "CRC", "CUP", "DOP", "GTQ", "HNL", "JMD",
    "MXN", "NIO", "PAB", "PEN", "PYG", "SVC", "UYU", "VES",
    # 大洋洲货币
    "NZD", "FJD", "PGK", "WST",
    # 非洲货币
    "ZAR", "EGP", "NGN", "KES", "GHS", "UGX", "TZS", "MWK", "MZN", "ZMW",
    "RWF", "BWP", "XOF", "XAF", "XCD", "XDR", "XAU", "XAG", "XPT", "XPD",
    "AOA", "BIF", "CVE", "DJF", "ERN", "ETB", "GMD", "GNF", "LYD", "MAD",
    "MGA", "MRO", "MUR", "NAD", "SCR", "SDG", "SLL", "SOS", "STD", "TND",
    "UAH", "XPF"
]

# 初始化数据库连接（全局复用）
DB_HELPER = MySQLHelper(database="shopify_dev")


# ===================== 核心功能 =====================
def get_latest_rates_from_db():
    """从数据库获取最新汇率数据（用于转换功能）"""
    try:
        conn = DB_HELPER._get_connection()
        with conn.cursor(dictionary=True) as cursor:
            # 获取最新日期的所有汇率数据
            latest_dt_sql = "SELECT MAX(dt) AS latest_dt FROM world_rate"
            cursor.execute(latest_dt_sql)
            result = cursor.fetchone()
            if not result or not result["latest_dt"]:
                raise ValueError("数据库中无汇率数据，请先执行数据获取")

            latest_dt = result["latest_dt"]
            select_sql = f"SELECT `from`, `to`, rate FROM world_rate WHERE dt = %s"
            cursor.execute(select_sql, (latest_dt,))
            db_data = cursor.fetchall()

            # 格式化数据为原字典结构
            rate_data = {}
            for item in db_data:
                to_curr = item["to"]
                rate_data[to_curr] = {
                    "rate": item["rate"],
                    "country": CURRENCY_COUNTRY_MAP.get(to_curr, "未知地区"),
                    "name": f"{to_curr}（{CURRENCY_COUNTRY_MAP.get(to_curr, '未知地区')}）"
                }
            return rate_data
    except Exception as e:
        raise Exception(f"从数据库获取汇率失败：{str(e)}")
    finally:
        conn.close()


def batch_get_world_rates(base_currency="USD", amount=1.0):
    """批量获取汇率数据并直接写入MySQL"""
    dt = datetime.now().strftime("%Y%m%d")
    conn = None
    try:
        # 建立数据库连接（单次任务复用）
        conn = DB_HELPER._get_connection()

        for target_currency in COMMON_CURRENCIES:
            country = CURRENCY_COUNTRY_MAP.get(target_currency, "未知地区")

            if target_currency == base_currency:
                # 同币种处理：直接写入数据库
                with conn.cursor() as cursor:
                    insert_sql = """
                        INSERT INTO world_rate (dt, amount, `from`, rate, `to`,country , insert_time)
                        VALUES (%s, %s, %s, %s, %s, %s, %s)
                    """
                    insert_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
                    cursor.execute(insert_sql, (dt, amount, base_currency, 1.0, target_currency,country, insert_time))
                    conn.commit()
                print(f"✅ 已写入 {base_currency} → {target_currency}（{country}） 汇率数据")
                time.sleep(0.2)
                continue

            # 发送请求获取汇率
            params = {
                "from": base_currency,
                "to": target_currency,
                "amount": amount
            }

            try:
                response = requests.get(API_URL, params=params, timeout=5,proxies={"http": None, "https": None})
                result = response.json()

                if result["code"] == 200:
                    data = result["result"] if "result" in result else result["data"]
                    # 适配接口返回格式（部分接口可能直接返回data字段）
                    rate = data.get("rate", 0)
                    result_amount = data.get("result", amount * rate)

                    with conn.cursor() as cursor:
                        insert_sql = """
                            INSERT INTO world_rate (dt, amount, `from`, rate, `to`,country , insert_time)
                            VALUES (%s, %s, %s, %s, %s, %s, %s)
                        """
                        insert_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
                        # 兼容接口可能的字段差异
                        insert_params = (
                            dt,
                            data.get("amount", amount),
                            data.get("from", base_currency),
                            rate,
                            data.get("to", target_currency),
                            country,
                            insert_time
                        )
                        cursor.execute(insert_sql, insert_params)
                        conn.commit()
                    logging.info(f"✅ 已写入 {base_currency} → {target_currency}（{country}） 汇率数据")
                else:
                    error_msg = result.get("msg", "未知错误")
                    logging.info(f"❌ {target_currency}（{country}） 获取失败：{error_msg}")

                time.sleep(4)  # 控制接口请求频率

            except Exception as e:
                logging.info(f"❌ {target_currency}（{country}） 处理失败：{str(e)}")
                continue

    except Exception as e:
        if conn:
            conn.rollback()
        logging.info(f"❌ 批量写入汇率数据失败：{str(e)}")
    finally:
        if conn:
            conn.close()


def fetch_and_save_daily_rates():
    """每日凌晨获取汇率并写入MySQL"""
    today = datetime.now().strftime("%Y-%m-%d")
    logging.info(f"\n===== {today} 开始获取汇率数据并写入MySQL =====")
    try:
        batch_get_world_rates("USD")
        logging.info(f"===== {today} 汇率数据写入完成 =====")
    except Exception as e:
        logging.info(f"===== {today} 汇率数据写入失败：{str(e)} =====")


def currency_convert(amount: float, from_currency: str, to_currency: str, rate_data=None):
    """货币转换（从数据库获取最新汇率）"""
    # 加载汇率数据（优先使用传入数据，否则从数据库获取）
    if not rate_data:
        rate_data = get_latest_rates_from_db()

    from_curr = from_currency.upper()
    to_curr = to_currency.upper()

    # 校验货币有效性
    if from_curr not in rate_data:
        raise ValueError(f"不支持的源货币：{from_curr}")
    if to_curr not in rate_data:
        raise ValueError(f"不支持的目标货币：{to_curr}")

    # 获取国家信息
    from_country = CURRENCY_COUNTRY_MAP.get(from_curr, "未知地区")
    to_country = CURRENCY_COUNTRY_MAP.get(to_curr, "未知地区")

    # 同币种转换
    if from_curr == to_curr:
        converted = round(amount, 4)
        print(f"💱 {amount} {from_curr}（{from_country}） = {converted} {to_curr}（{to_country}）")
        return converted

    from_rate = rate_data[from_curr]["rate"]  # 1 base_curr → from_curr的汇率
    to_rate = rate_data[to_curr]["rate"]  # 1 base_curr → to_curr的汇率
    from_to_rate = to_rate / from_rate  # 1 from_curr → to_curr的汇率
    converted_amount = round(amount * from_to_rate, 4)

    logging.info(f"💱 转换结果：{amount} {from_curr}（{from_country}） = {converted_amount} {to_curr}（{to_country}）")
    logging.info(f"📊 汇率明细：1 {from_curr} = {round(from_to_rate, 6)} {to_curr}")
    return converted_amount


def batch_convert(tasks: list):
    """批量转换任务（从数据库获取汇率）"""
    try:
        rate_data = get_latest_rates_from_db()
        for idx, task in enumerate(tasks, 1):
            try:
                amount, from_curr, to_curr = task
                print(f"\n===== 任务 {idx} =====")
                currency_convert(amount, from_curr, to_curr, rate_data)
            except Exception as e:
                print(f"\n===== 任务 {idx} 失败 =====")
                print(f"错误：{str(e)}")
    except Exception as e:
        print(f"批量转换初始化失败：{str(e)}")

